﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace MyOA.DAL
{
    public class DBHelper
    {

        string connectionString = ConfigurationManager.ConnectionStrings["MyOA"].ConnectionString;
        public DBHelper()
        {
            SqlConnection sqlcon = Connection;
            if(sqlcon==null)
            {
                sqlcon = new SqlConnection(connectionString);
                connection.Open();
                
            }  
            else if  (sqlcon.State == ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }
            else if (sqlcon.State == ConnectionState.Closed)
            {
                connection.Open();
            }

         
        }
        /// <summary>
        /// 链接属性
        /// </summary>
        private  SqlConnection connection;

        public  SqlConnection Connection
        {
            get
            {
                connection = new SqlConnection(connectionString);
                connection.Open();
                return connection;
            }

        }

        /// <summary>
        /// 关闭会话
        /// </summary>
        public  void CloseConnection()
        {

            connection.Close();
            connection.Dispose();

        }

        /// <summary>
        /// 增删改返回的影响行数
        /// </summary>
        /// <param name="storedProcedure">数据库存储过程名字</param>
        /// <returns></returns>
        public  int getCount(string storedProcedure)
        {
            int count = 0;
            SqlCommand command = new SqlCommand(storedProcedure,Connection);
            command.CommandType = CommandType.StoredProcedure;
            try
            {
               
                count = command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                string s = e.Message;
                count = 0;
            }
            CloseConnection();
            return count;
        }

        /// <summary>
        /// 增删改返回的影响行数(重载)
        /// </summary>
        /// <param name="storedProcedure">数据库存储过程名字</param>
        /// <param name="values">存储过程参数</param>
        /// <returns></returns>
        public  int getCount(string storedProcedure, params SqlParameter[] values)
        {
            int count = 0;
            SqlCommand command = new SqlCommand(storedProcedure, Connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddRange(values);
            try
            {
                
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                count = 0;
                Console.WriteLine(ex.Message);
            }
            CloseConnection();
            return count;
        }


        /// <summary>
        /// 是否查找到数据 用ExceuteScalar方法查找
        /// </summary>
        /// <param name="storedProcedure">存储过程名称</param>
        /// <returns></returns>
        public  object getScalar(string storedProcedure)
        {
            SqlCommand command = new SqlCommand(storedProcedure, Connection);
            command.CommandType = CommandType.StoredProcedure;

            object ob = null;
            try
            {
                
                ob = command.ExecuteScalar();
            }
            catch (Exception)
            {
                ob = null;
            }
            CloseConnection();
            return ob;
        }

        /// <summary>
        /// 是否查找到数据 用ExceuteScalar方法查找(重载)
        /// </summary>
        /// <param name="storedProcedure">存储过程名称</param>
        /// <param name="values">存储过程参数</param>
        /// <returns></returns>
        public  object getScalar(string storedProcedure, params SqlParameter[] values)
        {
            SqlCommand command = new SqlCommand(storedProcedure, Connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddRange(values);


            object ob = null;
            try
            {
              
                ob = command.ExecuteScalar();
            }
            catch (Exception e)
            {
                string s = e.Message;
                ob = null;
            }
            CloseConnection();
            return ob;
        }


        /// <summary>
        /// 获的sqldatareader
        /// </summary>
        /// <param name="procedure">存储过程</param>
        /// /// <param name="values">存储过程参数</param>
        /// <returns></returns>
        public  SqlDataReader getDataReader(string procedure)
        {
            SqlCommand command = new SqlCommand(procedure, Connection);
            command.CommandType = CommandType.StoredProcedure;

            SqlDataReader reader = command.ExecuteReader();

            return reader;
        }

        /// <summary>
        /// 获的sqldatareader
        /// </summary>
        /// <param name="procedure">存储过程</param>
        /// <returns></returns>
        public  SqlDataReader getDataReader(string procedure, params SqlParameter[] values)
        {
            SqlCommand command = new SqlCommand(procedure, Connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddRange(values);

            SqlDataReader reader = command.ExecuteReader();

            return reader;
        }

        /// <summary>
        /// 获得dataTable
        /// </summary>
        /// <param name="storedProcedure">存储过程名称</param>
        /// <returns></returns>
        public  DataTable getDataTable(string procedure)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(procedure, Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            return ds.Tables[0];


        }

        public DataTable getDataTable_1(string procedure)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(procedure, Connection);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            return ds.Tables[0];


        }

        /// <summary>
        /// 获得dataTable
        /// </summary>
        /// <param name="storedProcedure">存储过程名称</param>
        /// <param name="values">存储过程参数</param>
        /// <returns></returns>
        public  DataTable getDataTable(string procedure, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(procedure,Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(ds);

            return ds.Tables[0];


        }

        //////////////////////////
        //////////////////////////
        /////////////////////////////
        ////////////////////////////
        //SQL语句的DBHelper


        public DataTable GetDataSet1(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            connection.Close();
            connection.Dispose();
            return ds.Tables[0];
        }
    }
}
